/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package skv.db.daten;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceUnit;
import javax.persistence.TypedQuery;
import skv.db.MysqlHandler;
import skv.db.daten.entities.Benotungen;
import skv.db.daten.entities.Benutzer;
import skv.db.daten.entities.Fach;
import skv.db.daten.entities.Rolle;
import skv.db.daten.entities.Schulklasse;
import skv.db.iface.DbHandler;

/**
 * Der SkvDatenmanager bedient die SkvEntites. Er bietet auslese und Verarbeitungslogik an.
 * @author Alex
 */
public class SkvDatenmanager {
    private DbHandler dbh;
    
    @PersistenceUnit
    protected EntityManager em;

    public DbHandler getDbh() {
        return dbh;
    }

    public void setDbh(DbHandler dbh) {
        this.dbh = dbh;
    }

    public SkvDatenmanager(String Connectionstring) {
        dbh = new MysqlHandler(Connectionstring);
        dbh.connect();
    }
    public void disconnect(){
        dbh.disconnect();
    }
    public Rolle getUserRole(Benutzer b){
        return new Rolle(b.getRollenID(), dbh);
    }
    public Benutzer checkLoginData(String username, String password){
        try {
            ResultSet rst = dbh.doQuery("SELECT * FROM benutzer WHERE benLogin = '"+username+"' AND passwort = '"+password+"'");
            rst.last();
            if(rst.getRow() > 0 && rst.getRow() < 2){
                return new Benutzer(rst.getInt("benID"), dbh);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    public List<Benutzer> getAllUsers(){
        List<Benutzer> userList = new ArrayList<Benutzer>();
        ResultSet rs = dbh.doQuery("SELECT benID FROM benutzer");        
        try {
            while(rs.next())userList.add(new Benutzer(rs.getInt(1), dbh));
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return userList;
    }
    public List<Schulklasse> getAllClasses(){
        List<Schulklasse> classList = new ArrayList<Schulklasse>();
        
        ResultSet rs = dbh.doQuery("SELECT skID FROM schulklasse");        
        try {
            while(rs.next()){
                classList.add(new Schulklasse(rs.getInt(1), dbh));
            }
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return classList;
    }
    public List<Benutzer> getAllUserFromClass(Schulklasse s){
        try {
            List<Benutzer> userList = new ArrayList<Benutzer>();
            ResultSet rs =dbh.doQuery("SELECT benID FROM ben_klasse WHERE skID = " + s.getId());
            while(rs.next()){
                userList.add(new Benutzer(rs.getInt("benID"), dbh));
            }
            return userList;
            } catch (SQLException ex) {
                Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
            }
        return null;
    }

    public List<Schulklasse> getMyClasses(Benutzer b) {
        try {
            List<Schulklasse> classList = new ArrayList<Schulklasse>();
            ResultSet rs = dbh.doQuery("SELECT skID FROM ben_klasse WHERE benID = "+b.getId());
            while(rs.next())classList.add(new Schulklasse(rs.getInt("skid"), dbh));
            return classList;
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public double getAvgMarks(Schulklasse schulklasse){
        try {
            ResultSet rs = dbh.doQuery("SELECT benotungsNote FROM benotungen "
                    + "INNER JOIN benutzer ON benotungen.schuelerid = benutzer.benID "
                    + "INNER JOIN ben_klasse ON benutzer.benID = ben_klasse.benID "
                    + "WHERE skID = " + schulklasse.getId());
            double summe = 0;
            double notenTeiler = 0;
            while(rs.next()){
                summe+=rs.getInt("benotungsNote");
                notenTeiler++;
            }
            return summe/notenTeiler;
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public List<List<String>> getClassMarks(Schulklasse sk) {
        List<List<String>> returnList = new ArrayList<List<String>>();
        List<Benutzer> userList = getAllUserFromClass(sk);
        List<String> userStrings = new ArrayList<String>();
        List<String> avgMarkList = new ArrayList<String>();
        
        for(Benutzer user : userList){
            avgMarkList.add(getAvgMarkFromUser(user));
            userStrings.add(user.toString());
        }
        returnList.add(userStrings);
        returnList.add(avgMarkList);
        
        return returnList;
    }

    private String getAvgMarkFromUser(Benutzer user) {
        try {
            ResultSet rs = dbh.doQuery("SELECT benotungsNote FROM benotungen WHERE schuelerid = " + user.getId());
            double notensumme = 0;
            double notenanzahl = 0;
            while(rs.next()){
                notensumme += rs.getInt("benotungsNote");
                notenanzahl++;
            }
            return Double.toString(notensumme/notenanzahl);
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    public List<Benotungen> getUserMarks(Benutzer user){
        TypedQuery benotungenQuery = em.createNamedQuery("findAll", Benotungen.class);
        List<Benotungen> benotungen = benotungenQuery.getResultList();
        
        return benotungen;
    }
    public List<String> getAllRoles(){
        try {
            ResultSet rs = dbh.doQuery("SELECT rollenName FROM rollen");
            List<String> returnList = new ArrayList<String>();
            while(rs.next()){
                returnList.add(rs.getString("rollenName"));
            }
            return returnList;
        } catch (SQLException ex) {
            Logger.getLogger(SkvDatenmanager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    }
    

